/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.session;

import com.aptech.entity.RealEstate;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author Tuan Nguyen
 */
@Stateless
public class RealEstateFacade extends AbstractFacade<RealEstate> implements RealEstateFacadeLocal {

    @PersistenceContext(unitName = "mysweethome.com-ejbPU")
    private EntityManager em;

    protected EntityManager getEntityManager() {
        return em;
    }

    public RealEstateFacade() {
        super(RealEstate.class);
    }

    @Override
    public java.util.List<RealEstate> selectTop(String name) {
        Query q = em.createNativeQuery("SELECT TOP 5 * FROM RealEstate AS r WHERE r.RealEstateTypeID=(SELECT rt.RealEstateTypeID FROM RealEstateType AS rt WHERE rt.Name like ?value) AND r.IsActive='True' ORDER  BY r.RealEstateID ASC", RealEstate.class);
        q.setParameter("value", name);
        return q.getResultList();
    }

    @Override
    public java.util.List<RealEstate> selectByType(String name) {
        Query q = em.createNativeQuery("SELECT * FROM RealEstate AS r WHERE r.RealEstateTypeID=(SELECT rt.RealEstateTypeID FROM RealEstateType AS rt WHERE rt.Name like ?value) AND r.IsActive='True' ORDER  BY r.RealEstateID DESC", RealEstate.class);
        q.setParameter("value", name);
        return q.getResultList();
    }
    

    @Override
    public java.util.List<RealEstate> findHouse(int realEstateTypeID, int cityID, String location, double priceMin, double priceMax, double deposit, int bedrooms, int bathrooms, int houseTypeID) {
        String sql = "Select * from RealEState Where RealEstateTypeID =" + realEstateTypeID;
        if (cityID > 0) {
            sql += " AND CityID = " + cityID;
        }
        if (location.length() > 0) {
            sql += " AND Location like '%" + location + "%'";
        }
        if (priceMin > 0 && priceMin < priceMax) {
            sql += " AND Price IN (" + priceMin + "," + priceMax + ")";
        }
        if (deposit > 0) {
            sql += " AND Deposit < " + deposit;
        }
        if (bedrooms > 0) {
            sql += " AND NoOfBedrooms = " + bedrooms;
        }
        if (bathrooms > 0) {
            sql += " AND NoOfBathrooms= " + bathrooms;
        }
        if (houseTypeID > 0) {
            sql += " AND HouseTypeID =" + houseTypeID;
        }
        System.out.println("sql: " + sql);
        Query q = em.createNativeQuery(sql, RealEstate.class);
        return q.getResultList();
    }

    @Override
    public String sqlSearch(int realEstateTypeID, int cityID, String location, double priceMin, double priceMax, double deposit, int bedrooms, int bathrooms, int houseTypeID) {
        String sql = "Select * from RealEState Where RealEstateTypeID =" + realEstateTypeID;
        if (cityID > 0) {
            sql += " AND CityID = " + cityID;
        }
        if (location.length() > 0) {
            sql += " AND Location like '%" + location + "%'";
        }
        if (priceMin > 0 && priceMin < priceMax) {
            sql += " AND Price IN (" + priceMin + "," + priceMax + ")";
        }
        if (deposit > 0) {
            sql += " AND Deposit < " + deposit;
        }
        if (bedrooms > 0) {
            sql += " AND NoOfBedrooms = " + bedrooms;
        }
        if (bathrooms > 0) {
            sql += " AND NoOfBathrooms= " + bathrooms;
        }
        if (houseTypeID > 0) {
            sql += " AND HouseTypeID =" + houseTypeID;
        }
        return sql;
    }

    @Override
    public java.util.List<RealEstate> SearchRealEstate(String field, Object value) {
        Query q=em.createNativeQuery("SELECT * FROM RealEstate AS e WHERE "+field+" like ?value", RealEstate.class);
        q.setParameter("value", value);
        return q.getResultList();
    }
}
